package com.travel.dao.impl;

/**
* @Title RoomDaoImpl.java
* @Package com.travel.dao
* @Author ChenGuanXi
* @Time 2020年10月28日 上午10:11:08
* @Version 1.0
* @Description: TODO
*/
import java.util.List;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.travel.dao.RoomDao;
import com.travel.entity.Room;
import com.travel.util.DBUtils;
import com.travel.util.PageInfo;

public class RoomDaoImpl implements RoomDao {
	private DBUtils dbUtil = new DBUtils();

	/**
	 * 增
	 */
	@Override
	public int insertRoom(Room room) {
		String sql = "insert into tab_room values(null,?,?,?,?,?)";
		int i = dbUtil.update(sql, room.getHotelId(), room.getRoomTypeId(), room.getRoomPrice(), room.getRoomName(),room.getRoomPicture());
		return i;
	}

	/**
	 * 删
	 */
	@Override
	public int deleteRoom(int roomId) {
		String sql = "delete from tab_room where room_id = ?";
		int i = dbUtil.update(sql, roomId);
		return i;
	}

	/**
	 * 改
	 */
	@Override
	public int updateRoom(int roomId, Room room) {
		String sql = "update tab_room set hotel_id=?,room_type_id=?,room_price=?,room_name=? where room_id=?";
		int i = dbUtil.update(sql, room.getHotelId(), room.getRoomTypeId(), room.getRoomPrice(), room.getRoomName(),
				roomId);
		return i;
	}

//	/**
//	 * 查
//	 */
//	@Override
//	public List<Room> queryAllRooms() {
//		String sql = "select hotel_name,room_name,type_name,room_price from tab_room r join tab_hotel h on " + 
//				"r.hotel_id=h.hotel_id " + 
//				"join tab_room_type t on t.type_id=r.room_type_id";
//		RowSet rSet = dbUtil.query(sql);
//		List<Room> list = new ArrayList<Room>();
//		try {
//			while (rSet.next()) {
//				int roomId = rSet.getInt("room_id");
//				int hotelId = rSet.getInt("hotel_id");
//				int roomTypeId = rSet.getInt("room_type_id");
//				BigDecimal roomPrice = rSet.getBigDecimal("room_price");
//				String roomName = rSet.getString("room_name");
//
//				Room room = new Room(roomId, hotelId, roomTypeId, roomPrice, roomName);
//				list.add(room);
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		return list;
//	}

	/**
	 * 据当前页码、每页显示个数、酒店名查询酒店记录查询客房
	 * @throws SQLException 
	 */
	@Override
	public PageInfo<Room> queryRoomByPageAndName(int pageNum, int pageSize, String hotelName) throws SQLException {
		
		// 1.获取分页结果的数据
		List<Room> data = new ArrayList<Room>();
		StringBuffer sb = new StringBuffer();
		
		sb.append("select")
		  .append(" A.room_id,")
		  .append(" B.hotel_name,")
		  .append(" C.type_name,")
		  .append(" A.room_name,")
		  .append(" A.room_picture,")
		  .append(" A.room_price")
		  .append(" from tab_room A")
		  .append(" inner join tab_hotel B")
		  .append(" on A.hotel_id = B.hotel_id")
		  .append(" inner join tab_room_type C")
		  .append(" on A.room_type_id = C.type_id")
		  .append(" where hotel_name like concat('%',?,'%')")
		  .append(" limit ?,?");
		  
		ResultSet rs = dbUtil.query(sb.toString(), hotelName,(pageNum-1)*pageSize, pageSize);
		
		// 遍历结果集
		while (rs.next()) {
			
			int roomId = rs.getInt("room_id");
			String hotelName2 = rs.getString("hotel_name");
			String typeName = rs.getString("type_name");
			String roomName = rs.getString("room_name");
			String roomPicture = rs.getString("room_picture");
			BigDecimal roomPrice = rs.getBigDecimal("room_price");
			
			// 构建实体模型
			Room room = new Room(roomId,roomPrice, roomName, roomPicture, hotelName2, typeName);
			
			// 添加到集合中
			data.add(room);
		}
		
		// 2.获取总记录数(未分页之前)
		int total = 0;
		sb = new StringBuffer();
		sb.append("select")
		  .append(" count(*)")
		  .append(" from tab_room A")
		  .append(" inner join tab_hotel B")
		  .append(" on A.hotel_id = B.hotel_id")
		  .append(" inner join tab_room_type C")
		  .append(" on A.room_type_id = C.type_id")
		  .append(" where hotel_name like concat('%',?,'%')");
		rs  = dbUtil.query(sb.toString(), hotelName);
		// 遍历结果集
		try {
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// 构建分页对象
		PageInfo<Room> pageInfo = new PageInfo<Room>(data, pageNum, pageSize, total);
		return pageInfo;
	}

	/**
	 * 根据RoomId查询tab_room记录
	 * @throws SQLException 
	 */
	@Override
	public List<Room> getRoomById(int roomId) throws SQLException {

		List<Room> list = new ArrayList<Room>();
		StringBuffer sb = new StringBuffer();
		sb.append("select")
		  .append(" A.room_id,")
		  .append(" B.hotel_name,")
		  .append(" C.type_name,")
		  .append(" A.room_name,")
		  .append(" A.room_picture,")
		  .append(" A.room_price")
		  .append(" from tab_room A")
		  .append(" inner join tab_hotel B")
		  .append(" on A.hotel_id = B.hotel_id")
		  .append(" inner join tab_room_type C")
		  .append(" on A.room_type_id = C.type_id")
		  .append(" where room_id = ?");
		
		ResultSet rs = dbUtil.query(sb.toString(), roomId);
		// 遍历结果集
		while (rs.next()) {
			
			int roomId2 = rs.getInt("room_id");
			String hotelName2 = rs.getString("hotel_name");
			String typeName = rs.getString("type_name");
			String roomName = rs.getString("room_name");
			String roomPicture = rs.getString("room_picture");
			BigDecimal roomPrice = rs.getBigDecimal("room_price");
			
			// 构建实体模型
			Room room = new Room(roomId2,roomPrice, roomName, roomPicture, hotelName2, typeName);
			
			// 添加到集合中
			list.add(room);
		}
		
		return list;
	}

	@Override
	public int modifyRoomWithNoPicture(Room room) {
		
		String sql = "update tab_room set room_type_id=? ,room_name=?,room_price=? where room_id=?";
		int i = dbUtil.update(sql,room.getRoomTypeId(), room.getRoomName(),room.getRoomPrice(),room.getRoomId());

		return i;
	}

	@Override
	public int modifyRoomWithPicture(Room room) {

		String sql = "update tab_room set room_type_id=? ,room_name=?,room_price=?,room_picture=? where room_id=?";
		int i = dbUtil.update(sql, room.getRoomTypeId(), room.getRoomName(),room.getRoomPrice(),room.getRoomPicture(),room.getRoomId());
		return i;
	}

	
	@Override
	public List<Room> queryRoomsByHotelId(int hotelId) {
		
		List<Room> list = new ArrayList<Room>();
		StringBuffer sql = new StringBuffer();
		sql.append("select")
		  .append(" A.room_name,")
		  .append(" A.room_price,")
		  .append(" A.room_picture,")
		  .append(" B.type_name")
		  .append(" from tab_room A")
		  .append(" inner join tab_room_type B")
		  .append(" on A.room_type_id = B.type_id")
		  .append(" inner join tab_hotel C")
		  .append(" on A.hotel_id = C.hotel_id")
		  .append(" where C.hotel_id = ?");
		
		ResultSet rs = dbUtil.query(sql.toString(), hotelId);
		// 遍历结果集
		try {
			while (rs.next()) {
				
				String roomName = rs.getString("room_name");
				String roomPrice = rs.getString("room_price");
				String roomPicture = rs.getString("room_picture");
				String roomTypeName = rs.getString("type_name");
				
				// 构建实体模型
				Room room = new Room(null,new BigDecimal(roomPrice),roomName,roomPicture,null,roomTypeName);
				
				// 添加到集合中
				list.add(room);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}

	

}
